package com.mairuide.bill.information;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.bill.DealFlow;
import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.entity.PageResult;
import com.mairuide.phone.PhoneRecord;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * person service 
 * @author mh
 *
 */

@Service
@Transactional
public class PersonInformationService extends BaseServiceProxy<PersonInformation> {
	@Resource
	private PersonInformationRepository personInformationRepository;
	
	@Autowired
	private CaseManageService caseManageService; 
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@PersistenceContext
	private EntityManager entityManager;
	
	@Override
	protected BaseRepository<PersonInformation> getBaseRepository() {
		return personInformationRepository;
	}
	
	/**
	 * 根据身份证号查询用户信息
	 * @param idNumber
	 * @return
	 */
	public PersonInformation findPersonInformationByIdNumber(String idNumber) {
		List<PersonInformation> number = personInformationRepository.findByIdNumber(idNumber);
		return number.size()==0?null:number.get(0);
	}
	
	/**
	 * 分页查询案件信息
	 * @param keyword
	 * @param page
	 * @param rows
	 * @param integer
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult<PersonInformation> findPersonInformationByPage(PersonInformationParams pp) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("SELECT T.P_ID AS pId,T.PERSON_NAME AS personName,T.ID_NUMBER AS idNumber,"); 
		selectSql.append("T.ID_TYPE as idType,T.GENDER as gender,t.BIRTH_DATE AS birthDate,t.BODY_TYPE AS bodyType,"); 
		selectSql.append("T.CASEMANAGER_ID AS casemanagerId,T.PHONE_NUMBER AS phoneNumber,T.BIRTH_PLACE AS birthPlace,T.REMARK AS remark ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" FROM TB_PERSON_INFORMATION T ");
		fromWhereSql.append("WHERE T.CASEMANAGER_ID = '"+pp.getCasemanagerId()+"' ");
		if(StringUtils.isNotEmpty(pp.getKeyword())) {
			fromWhereSql.append("and (T.PERSON_NAME like '%"+pp.getKeyword()+"%' or T.ID_NUMBER like '%"+pp.getKeyword()+"%') ");
		}
		if(StringUtils.isNotEmpty(pp.getSort())) {
			fromWhereSql.append("order by "+pp.getSort()+" "+pp.getOrder());
		}else {
			fromWhereSql.append("order by T.P_ID desc");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PersonInformation.class));		//设置分页
		query.setFirstResult((pp.getPage() - 1) * pp.getRows());
		query.setMaxResults(pp.getRows());
		//获取查询结果集
		List<PersonInformation> list = query.getResultList();
		PageResult<PersonInformation> pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	/**
	 * 查询证件类型下拉菜单
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<String> findIdType(){
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select id_type from tb_person_information t where t.id_type is not null group by t.id_type");
		Query query = entityManager.createNativeQuery(selectSql.toString());
		List<String> result = query.getResultList();
		return result;
	}
	
	/**
	 * 批量更新
	 * @param list
	 */
	public void batchSave(List<PersonInformation> list) {
        if(list!=null && list.size()>0){
        	for(int i = 0; i< list.size() ; i++) {
        		PersonInformation pip = list.get(i);
        		PersonInformation pf = findPersonInformationByIdNumber(list.get(i).getIdNumber());
        		if(null != pf) {
        			pf.setBirthPlace(pip.getBirthPlace());
        			pf.setIdNumber(pip.getIdNumber());
        			pf.setIdType(pip.getIdType());
        			pf.setPhoneNumber(pip.getPhoneNumber());
        			personInformationRepository.save(pf);
        		}else {
        			final List<PersonInformation> tempList = list;
        			String sql="insert into TB_PERSON_INFORMATION(P_ID, PERSON_NAME, ID_TYPE, ID_NUMBER, PHONE_NUMBER, BIRTH_DATE," +
        					"BIRTH_PLACE,DATA_SOURCE,CASEMANAGER_ID) ";
        			sql+=" values(SEQ_PERSON_INFORMATION.nextval,?,?,?,?,?,?,?,?) ";
        			jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        				public void setValues(PreparedStatement ps, int i) throws SQLException {
        					PersonInformation info = tempList.get(i);
        					ps.setString(1, info.getPersonName()==null ? "" : info.getPersonName());
        					ps.setString(2, info.getIdType()==null ? "" : info.getIdType());
        					ps.setString(3, info.getIdNumber()==null ? "" : info.getIdNumber());
        					ps.setString(4, info.getPhoneNumber()==null ? "" : info.getPhoneNumber());
        					ps.setTimestamp(5, info.getBirthDate()==null ? null : info.getBirthDate());
        					ps.setString(6, info.getBirthPlace()==null ? null : info.getBirthPlace());
        					ps.setString(7, "系统导入");
        					ps.setString(8, info.getCasemanagerId()==null ? null : info.getCasemanagerId());
        				}
        				public int getBatchSize() {
        					return tempList.size();
        				}
        			});
        		}
        	}
        }
    }

	/**
	 * 根据交易主体卡号身份证号统计该主题下交易清单
	 * @param accountBody
	 * @param idNumber
	 * @param rownum
	 * @param casemanageId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<String> findAccountBodyCardsByAccountBoydAndIdNumber(String accountBody, String idNumber, String casemanageId) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sbf = new StringBuffer();
		sbf.append("select account_body_card from "+whichTable+" t ");
		sbf.append("where t.account_body = '"+accountBody+"' and t.id_number='"+idNumber+"' group by account_body_card");
		Query query = entityManager.createNativeQuery(sbf.toString());
		List<String> result = query.getResultList();
		return result;
	}
	
	/**
	 * 根据卡号集合删除交易流水信息
	 * @param cards
	 * @param casemanageId
	 */
	public void deleteDealFlowsByAccountBodyCard(List<String> cards,String casemanageId) {
		String whichTable = caseManageService.getWhichTable(casemanageId);
		StringBuffer sbf = new StringBuffer();
		sbf.append("delete from "+whichTable+" t ");
		sbf.append("where t.account_body_card in('"+StringUtils.join(cards,"','")+"') and t.CASEMANAGE_ID='"+casemanageId+"'");
		jdbcTemplate.execute(sbf.toString());
	}
	
	/**
	 * 根据姓名查询人员信息
	 * @param personName
	 * @return
	 */
	public PersonInformation findByPersonName(String personName) {
		return personInformationRepository.findByPersonName(personName);
	}
	
	/**
	 * 根据姓名证件号码查询人员信息
	 * @param personName
	 * @return
	 */
	public PersonInformation findByPersonNameAndIdNumber(String personName,String idNumber) {
		return personInformationRepository.findByPersonNameAndIdNumber(personName,idNumber);
	}
	
	public void deleteByPersonName(String personName) {
		personInformationRepository.deleteByPersonName(personName);
	}
	
	public void deleteByCasemanagerId(String casemanagerId) {
		personInformationRepository.deleteByCasemanagerId(casemanagerId);
	}

	//通过名字 或者 身份证  来查询人员信息
	public List<PersonInformation> queryPersonInformationByPersonNameOrIdNumber(String keyword) {
        String sql = "select P_ID pId, PERSON_NAME personName, ID_NUMBER idNumber from  TB_PERSON_INFORMATION WHERE PERSON_NAME is not null and ID_NUMBER is not null ";
        if (org.apache.commons.lang3.StringUtils.isNotBlank(keyword)) {
            sql += " and PERSON_NAME like '%" + keyword + "%' OR ID_NUMBER like '%" + keyword + "%' ";
        }
        List<PersonInformation> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<PersonInformation>(PersonInformation.class));
        return list;
    }
	
	/**
	  * 查询导入账单文件下所有未关联上的案件对象
	 * @param pp
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult<DealFlow> findCaseObjectMapping(PersonInformationParams pp) {
		List<String> fileUuids = StringUtils.removeDuplicateList(pp.getFileUuids());
		String whichTable = caseManageService.getWhichTable(pp.getCasemanagerId());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select t.account_body_card,t.id_number ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" from "+whichTable);
		fromWhereSql.append(" t where t.uuid in ('"+StringUtils.join(fileUuids,"','")+"') ");
		fromWhereSql.append("and t.account_body is null ");
		if(StringUtils.isNotEmpty(pp.getAccountBodyCard())) {
			fromWhereSql.append(" and t.account_body_card like '%"+pp.getAccountBodyCard()+"%' ");
		}
		fromWhereSql.append(" and t.account_body_card is not null group by t.account_body_card,t.id_number ");
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(DealFlow.class));//设置分页
		query.setFirstResult((pp.getPage() - 1) * pp.getRows());
		query.setMaxResults(pp.getRows());
		//获取查询结果集
		List<DealFlow> list = query.getResultList();
		PageResult<DealFlow> pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	/**
	  * 查询导入话单文件下所有未关联上的案件对象
	 * @param pp
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageResult<PhoneRecord> findCaseObjectPhoneMapping(PersonInformationParams pp) {
		List<String> fileUuids = StringUtils.removeDuplicateList(pp.getFileUuids());
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select t.service_number,t.id_number ");
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" from TB_PHONE_RECORDING t");
		fromWhereSql.append(" where t.uuid in ('"+StringUtils.join(fileUuids,"','")+"') ");
		fromWhereSql.append("and t.SERVICE_USER_INFORMATION is null and t.CASEMANAGE_ID='"+pp.getCasemanagerId()+"' ");
		if(StringUtils.isNotEmpty(pp.getServiceNumber())) {
			fromWhereSql.append(" and t.service_number like '%"+pp.getServiceNumber()+"%' ");
		}
		fromWhereSql.append(" and t.service_number is not null group by t.service_number,t.id_number ");
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(PhoneRecord.class));//设置分页
		query.setFirstResult((pp.getPage() - 1) * pp.getRows());
		query.setMaxResults(pp.getRows());
		//获取查询结果集
		List<PhoneRecord> list = query.getResultList();
		PageResult<PhoneRecord> pageResult = new PageResult(count,list);
		return pageResult;
	}
	
	/**
	 * 账单更新案件对象
	 * @param pp
	 */
	public void updateCaseObjectBillMapping(PersonInformationParams pp) {
		String whichTable = caseManageService.getWhichTable(pp.getCasemanagerId());
		StringBuffer updateDealflowSql = new StringBuffer();
		updateDealflowSql.append("update "+ whichTable +" t set t.id_number='"+pp.getIdNumber()+"',t.account_body='"+pp.getPersonName()+"' where t.account_body_card='"+pp.getAccountBodyCard()+"'");
		jdbcTemplate.execute(updateDealflowSql.toString());
		PersonInformation p1 = personInformationRepository.findByPersonName(pp.getPersonName());
		List<PersonInformation> p2 = personInformationRepository.findByIdNumber(pp.getIdNumber());
		if(p1 != null) {
			StringBuffer updatePiSqlByPersonName = new StringBuffer();
			updatePiSqlByPersonName.append("update tb_person_information t set t.id_number='"+pp.getIdNumber()+"',t.id_type='"+pp.getIdType()+"' where t.person_name='"+pp.getPersonName()+"'");
			jdbcTemplate.execute(updatePiSqlByPersonName.toString());   
		}else if(p2 != null && !p2.isEmpty()) {
			StringBuffer updatePiSqlByIdNumber = new StringBuffer();
			updatePiSqlByIdNumber.append("update tb_person_information t set t.person_name='"+pp.getPersonName()+"',t.id_type='"+pp.getIdType()+"' where t.id_number='"+pp.getIdNumber()+"'");
			jdbcTemplate.execute(updatePiSqlByIdNumber.toString());
		}else {
			StringBuffer insertPISql = new StringBuffer();
			insertPISql.append("insert into TB_PERSON_INFORMATION(P_ID, PERSON_NAME, ID_TYPE, ID_NUMBER,DATA_SOURCE,CASEMANAGER_ID) ");
			insertPISql.append(" values(SEQ_PERSON_INFORMATION.nextval,'"+pp.getPersonName()+"','"+pp.getIdType()+"','"+pp.getIdNumber()+"','案件对象维护','"+pp.getCasemanagerId()+"')");
			jdbcTemplate.execute(insertPISql.toString());
		}
	}
	
	/**
	 * 话单更新案件对象
	 * @param pp
	 */
	public void updateCaseObjectPhoneMapping(PersonInformationParams pp) {
		StringBuffer updateDealflowSql = new StringBuffer();
		updateDealflowSql.append("update TB_PHONE_RECORDING t set t.id_number='"+pp.getIdNumber()+"',t.SERVICE_USER_INFORMATION='"+pp.getPersonName()+"' where t.service_number='"+pp.getServiceNumber()+"'");
		jdbcTemplate.execute(updateDealflowSql.toString());
		PersonInformation p1 = personInformationRepository.findByPersonName(pp.getPersonName());
		List<PersonInformation> p2 = personInformationRepository.findByIdNumber(pp.getIdNumber());
		if(p1 != null) {
			StringBuffer updatePiSqlByPersonName = new StringBuffer();
			updatePiSqlByPersonName.append("update tb_person_information t set t.id_number='"+pp.getIdNumber()+"',t.id_type='"+pp.getIdType()+"',t.PHONE_NUMBER='"+pp.getServiceNumber()+"' where t.person_name='"+pp.getPersonName()+"'");
			jdbcTemplate.execute(updatePiSqlByPersonName.toString());
		}else if(p2 != null && !p2.isEmpty()) {
			StringBuffer updatePiSqlByIdNumber = new StringBuffer();
			updatePiSqlByIdNumber.append("update tb_person_information t set t.person_name='"+pp.getPersonName()+"',t.id_type='"+pp.getIdType()+"',t.PHONE_NUMBER='"+pp.getServiceNumber()+"' where t.id_number='"+pp.getIdNumber()+"'");
			jdbcTemplate.execute(updatePiSqlByIdNumber.toString());
		}else {
			StringBuffer insertPISql = new StringBuffer();
			insertPISql.append("insert into TB_PERSON_INFORMATION(P_ID, PERSON_NAME, ID_TYPE, ID_NUMBER,DATA_SOURCE,CASEMANAGER_ID,PHONE_NUMBER) ");
			insertPISql.append(" values(SEQ_PERSON_INFORMATION.nextval,'"+pp.getPersonName()+"','"+pp.getIdType()+"','"+pp.getIdNumber()+"','案件对象维护','"+pp.getCasemanagerId()+"','"+pp.getServiceNumber()+"')");
			jdbcTemplate.execute(insertPISql.toString());
		}
	}
}
